﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EnglishLibrary;
using System.Data.OleDb;
using Helper;

namespace DataAccess
{
    //create, read, delete, update vocabulary items
    public class VocabularyDAO
    {
        private string _filePath;

        public string FilePath
        {
            get { return _filePath; }
            set { _filePath = value; }
        }

        public VocabularyDAO(string profilePath)
        {
            _filePath = profilePath;
        }

        public VocabularyDAO()
        {
            _filePath = string.Empty;
        }

        #region Get
        public List<Vocabulary> GetListItems(bool isGetDeleted)
        {
            var list = new List<Vocabulary>();
            var query =
                //"SELECT ITEMID, WORD, MEANING, PRONUNCIATION, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                "SELECT * " +
                "FROM VOCABULARY ";

            if (!isGetDeleted)
                query += "WHERE ISDELETED = false";

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var vocabularyItem = new Vocabulary();

                    if (!reader.IsDBNull(0))
                        vocabularyItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        vocabularyItem.Word = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        vocabularyItem.Meaning = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        vocabularyItem.Pronunciation = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        vocabularyItem.Example = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        vocabularyItem.RateID = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        vocabularyItem.CreatedDate = reader.GetDateTime(6);
                    if (!reader.IsDBNull(7))
                        vocabularyItem.ModifiedDate = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        vocabularyItem.CateID = reader.GetInt32(8);
                    if (!reader.IsDBNull(9))
                        vocabularyItem.IsDeleted = reader.GetBoolean(9);

                    list.Add(vocabularyItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }

        //Get vocabulary by ID
        public Vocabulary GetItemByID(int id)
        {
            Vocabulary vocabularyItem = null;

            try
            {
                DataProvider.OpenConnection(_filePath);
                var query = String.Format("SELECT ITEMID, WORD, MEANING, PRONUNCIATION, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                          "FROM VOCABULARY " +
                                          "WHERE ITEMID = {0}", id);

                var reader = DataProvider.ExecuteReader(query);

                if (reader.Read())
                {
                    vocabularyItem = new Vocabulary();
                    if (!reader.IsDBNull(0))
                        vocabularyItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        vocabularyItem.Word = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        vocabularyItem.Meaning = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        vocabularyItem.Pronunciation = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        vocabularyItem.Example = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        vocabularyItem.RateID = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        vocabularyItem.CreatedDate = reader.GetDateTime(6);
                    if (!reader.IsDBNull(7))
                        vocabularyItem.ModifiedDate = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        vocabularyItem.CateID = reader.GetInt32(8);
                    if (!reader.IsDBNull(9))
                        vocabularyItem.IsDeleted = reader.GetBoolean(9);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return vocabularyItem;
        }
        
        //Get list items before date
        public List<Vocabulary> GetItemsBeforeDate(DateTime date)
        {
            var list = new List<Vocabulary>();
            var query = String.Format("SELECT ITEMID, WORD, MEANING, PRONUNCIATION, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                      "FROM VOCABULARY " +
                                      "WHERE CREATEDATE <= #{0}#", date.ToShortDateString());

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var vocabularyItem = new Vocabulary();

                    if (!reader.IsDBNull(0))
                        vocabularyItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        vocabularyItem.Word = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        vocabularyItem.Meaning = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        vocabularyItem.Pronunciation = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        vocabularyItem.Example = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        vocabularyItem.RateID = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        vocabularyItem.CreatedDate = reader.GetDateTime(6);
                    if (!reader.IsDBNull(7))
                        vocabularyItem.ModifiedDate = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        vocabularyItem.CateID = reader.GetInt32(8);
                    if (!reader.IsDBNull(9))
                        vocabularyItem.IsDeleted = reader.GetBoolean(9);

                    list.Add(vocabularyItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }

        //Get list items after date
        public List<Vocabulary> GetItemsAfterDate(DateTime date)
        {
            var list = new List<Vocabulary>();
            var query = String.Format("SELECT ITEMID, WORD, MEANING, PRONUNCIATION, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                      "FROM VOCABULARY " +
                                      "WHERE CREATEDDATE >= #{0}#", date.ToShortDateString());

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var vocabularyItem = new Vocabulary();

                    if (!reader.IsDBNull(0))
                        vocabularyItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        vocabularyItem.Word = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        vocabularyItem.Meaning = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        vocabularyItem.Pronunciation = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        vocabularyItem.Example = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        vocabularyItem.RateID = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        vocabularyItem.CreatedDate = reader.GetDateTime(6);
                    if (!reader.IsDBNull(7))
                        vocabularyItem.ModifiedDate = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        vocabularyItem.CateID = reader.GetInt32(8);

                    list.Add(vocabularyItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }

        //Get list items by cate
        public List<Vocabulary> GetItemsByCates(List<int> catesID)
        {
            var list = new List<Vocabulary>();
            //check empty
            if (catesID.Count <= 0)
                return list;

            var query = String.Format("SELECT ITEMID, WORD, MEANING, PRONUNCIATION, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                      "FROM VOCABULARY " +
                                      "WHERE {0}", QueryHelper.GenerateQueryStringByCateId(catesID));

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var vocabularyItem = new Vocabulary();

                    if (!reader.IsDBNull(0))
                        vocabularyItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        vocabularyItem.Word = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        vocabularyItem.Meaning = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        vocabularyItem.Pronunciation = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        vocabularyItem.Example = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        vocabularyItem.RateID = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        vocabularyItem.CreatedDate = reader.GetDateTime(6);
                    if (!reader.IsDBNull(7))
                        vocabularyItem.ModifiedDate = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        vocabularyItem.CateID = reader.GetInt32(8);

                    list.Add(vocabularyItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }
        #endregion

        #region Insert items
        public bool InsertItem(Vocabulary vocabularyItem)
        {
            //ISDELETED field is false by default
            var query = String.Format("INSERT INTO VOCABULARY(WORD, MEANING, PRONUNCIATION, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID) " +
                                      "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', {7})", 
                                      QueryHelper.Validate(vocabularyItem.Word), 
                                      QueryHelper.Validate(vocabularyItem.Meaning), 
                                      QueryHelper.Validate(vocabularyItem.Pronunciation), 
                                      QueryHelper.Validate(vocabularyItem.Example), 
                                      vocabularyItem.RateID, vocabularyItem.CreatedDate, vocabularyItem.ModifiedDate, vocabularyItem.CateID);

            try
            {
                DataProvider.OpenConnection(_filePath);
                DataProvider.ExecuteNonquery(query);
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return true;
        }
        #endregion

        #region Update item
        public bool UpdateItem(Vocabulary vocabularyItem)
        {
            //TODO: [PHUC] write IsUnique or IsExist method
            var tempVocabulary = this.GetItemByID(vocabularyItem.ItemID);

            if (tempVocabulary == null)
                return false;

            try
            {
                DataProvider.OpenConnection(_filePath);

                var query = String.Format("UPDATE VOCABULARY SET WORD = \"{0}\", MEANING = \"{1}\", PRONUNCIATION = \"{2}\", " +
                                                                   "EXAMPLE = \"{3}\", RATEID = \"{4}\", MODIFIEDDATE = \"{5}\" " +
                                             "WHERE ITEMID = {6}", 
                                             QueryHelper.Validate(vocabularyItem.Word), 
                                             QueryHelper.Validate(vocabularyItem.Meaning), 
                                             QueryHelper.Validate(vocabularyItem.Pronunciation), 
                                             QueryHelper.Validate(vocabularyItem.Example), 
                                             vocabularyItem.RateID, DateTime.Now.ToShortDateString(), vocabularyItem.ItemID);

                DataProvider.ExecuteNonquery(query);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }
        #endregion

        #region Delete item
        //not truly delete, just mark it "deleted"
        public bool DeleteItem(int vocabularyId)
        {
            //TODO: [PHUC] write IsUnique or IsExist method
            var tempVocabulary = this.GetItemByID(vocabularyId);

            if (tempVocabulary == null)
                return false;

            try
            {
                DataProvider.OpenConnection(_filePath);

                var query = String.Format("UPDATE VOCABULARY SET ISDELETED = true WHERE ITEMID = {0}", vocabularyId);

                DataProvider.ExecuteNonquery(query);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }
        #endregion
    }
}
